capture log close
clear all
set maxvar 30000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table B16
RD and IV estimates of criminal justice outcomes (siblings, 18-year-old plus siblings, parents)
*/


/* ******************************************** */
/* **** PREPARE DATA: SIBLINGS AND PARENTS **** */
/* ******************************************** */

foreach gr in sib par {;

use "`crime'/prwora`gr'_crime_summstats.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;

preserve ;

		keep	`pre_all'n_crime2char* 
				`pre_all'n_crime2genr* `pre_all'n_crime2nogn*  
				`pre_all'n_crime2ahinc* `pre_all'n_crime2shinc* 
				;

		xpose, clear varname ;

		rename v1 mean ;
		rename _varname variables ;
		order variables mean ;

		replace variables = subinstr(variables,"_1997_2017", "", .) ;
		replace variables = subinstr(variables,"`pre_all'n_crime2", "", .) ;
		replace variables = subinstr(variables,"`pre_all'n_", "", .) ;
		replace variables = "age18_yes" if variables == "`pre_all'age18_yes" ; 
; 

		tempfile all_mean ;
		save `all_mean' ;

restore ;

clear;


foreach method in rd iv {;
	use "`crime'/prwora`gr'_bw264_crime_poly1allcov_`method'_all.dta" ;

	#delim;
	gen n_crime2 = strpos(variables, "n_crime2") ;

	keep if n_crime2 == 1 | variables == "age18_yes" | variables == "n_age18unfav" 
	| variables == "n_onssi1922" ;

			
	if "`method'" == "rd" {;

		keep variables n_post sen_post N ;
			
	};
			
	else {;

		keep if n_crime2 == 1 ;			
		keep variables n_age18unfav sen_age18unfav N;
		};

		replace variables = subinstr(variables,"_1997_2017", "", .) ;
		replace variables = subinstr(variables,"n_crime2", "", .) ;
		replace variables = subinstr(variables,"n_", "", .) ;

	#delim;

	if "`method'" == "rd" {;
			
		local crime "char genr nogn ahinc shinc" ;

	};
			
	else {;
			
		local crime "char genr nogn ahinc shinc" ;			
	};
						 
	gen keep = strpos(variables, "char") ;

	foreach var in `crime' {;

	replace keep = strpos(variables, "`var'") if keep == 0;

	};

	drop if keep == 0 ;
	drop keep ;


	merge 1:1 variables using `all_mean' ;
	drop _merge ;


	gen flag_int = strpos(variables, "t") ;

	gen length = length(variables) ;
	replace flag_int = 1 if variables == "shinc" | variables == "sipp" ;			
	replace flag_int = 1 if flag_int != 0 & length == 5;
	replace flag_int = 0 if flag_int != 1 ;

	replace variables = "Any charge" if variables == "char" | variables == "chart" ;	
	replace variables = "Income-generating charge" if variables == "genr" | variables == "genrt" ;
	replace variables = "Non-income-generating" if variables == "nogn" | variables == "nognt" ;	
	replace variables = "Incarcerated (annual likelihood/days)" if variables == "ahinc" | variables == "shinc" ;		
	
	sort variables flag_int ;

	if "`method'" == "rd" {;

		rename n_post pt_est_rd ;
		rename sen_post se_rd ;
		gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
		gen sta_sign_rd = "" ;
		replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
		replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
		replace sta_sign_rd = "***" if star_rd <= 0.01 ;
		drop star_rd ;
	};
			
	else {;
				
		rename n_age18unfav pt_est_iv ;
		rename sen_age18unfav se_iv ;
		gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
		gen sta_sign_iv = "" ;
		replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
		replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
		replace sta_sign_iv = "***" if star_iv <= 0.01 ;
		drop star_iv ;
	};
			
			
	order variables pt_est* se* sta_sign* mean N flag_int;
			
			
	tostring flag_int, replace ;
			
	replace flag_int = "_in" if flag_int == "1" ;
	replace flag_int = "_ex" if flag_int == "0" ;
			
	drop length ;
			
	reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

	tostring se*, replace ;
	replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
	replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
	tempfile all_`method'_all ;
	save `all_`method'_all' ;
			
	}; 
		

use `all_rd_all', clear ;
		
merge 1:1 variables using `all_iv_all' ;
drop _merge ;
		
		
order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
				pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
gen order = 1 if variables == "Any charge" ;
replace order = 2 if variables == "Income-generating charge" ;
replace order = 3 if variables == "Non-income-generating" ;
replace order = 4 if variables == "Incarcerated (annual likelihood/days)" ;

			
sort order ;
		
drop order ;
		
tempfile compiled_all_`gr' ;
save `compiled_all_`gr'' ;	

};


/* ***************************************** */
/* **** PREPARE DATA: 18yo AND SIBLINGS **** */
/* ***************************************** */

use "`crime'/prworacombsib_crime_summstats.dta", clear ;

keep if label == "prec_rd_mean" ;

local pre_all "a" ;

preserve ;

keep	`pre_all'any_crime2char* 
		`pre_all'any_crime2genr* `pre_all'any_crime2nogn*  
		`pre_all'any_crime2ahinc* `pre_all'any_crime2shinc* 
		;

xpose, clear varname ;

rename v1 mean ;
rename _varname variables ;
order variables mean ;

replace variables = subinstr(variables,"_1997_2017", "", .) ;
replace variables = subinstr(variables,"`pre_all'any_crime2", "", .) ;
replace variables = subinstr(variables,"`pre_all'any_", "", .) ;
replace variables = "age18_yes" if variables == "`pre_all'age18_yes" ; 

tempfile all_mean ;
save `all_mean' ;

restore ;


clear;


foreach method in rd iv {;
	use "`crime'/prworacombsib_bw264_crime_poly1allcov_`method'_all.dta" ;

	#delim;
	gen any_crime2 = strpos(variables, "any_crime2") ;

	keep if any_crime2 == 1 | variables == "age18_yes" | variables == "any_age18unfav" 
			| variables == "any_onssi1922" ;

			
	if "`method'" == "rd" {;

	keep variables n_post sen_post N ;
			
	};
			
	else {;

		keep if any_crime2 == 1 ;
				
		keep variables n_age18unfav sen_age18unfav N;
	};

	replace variables = subinstr(variables,"_1997_2017", "", .) ;
	replace variables = subinstr(variables,"any_crime2", "", .) ;
	replace variables = subinstr(variables,"any_", "", .) ;

	#delim;

	if "`method'" == "rd" {;
			
	local crime "char genr nogn ahinc shinc" ;

	};
			
	else {;
			
	local crime "char genr nogn ahinc shinc" ;

	};
						 
	gen keep = strpos(variables, "char") ;

	foreach var in `crime' {;

		replace keep = strpos(variables, "`var'") if keep == 0;

	};

	drop if keep == 0 ;
	drop keep ;

	merge 1:1 variables using `all_mean' ;
	drop _merge ;


	gen flag_int = strpos(variables, "t") ;
	gen length = length(variables) ;
	replace flag_int = 1 if variables == "shinc" | variables == "sipp" ;		
	replace flag_int = 1 if flag_int != 0 & length == 5;
	replace flag_int = 0 if flag_int != 1 ;
			
	replace variables = "Any charge" if variables == "char" | variables == "chart" ;	
	replace variables = "Income-generating charge" if variables == "genr" | variables == "genrt" ;
	replace variables = "Non-income-generating" if variables == "nogn" | variables == "nognt" ;	
	replace variables = "Incarcerated (annual likelihood/days)" if variables == "ahinc" | variables == "shinc" ;	

	sort variables flag_int ;

	if "`method'" == "rd" {;

		rename n_post pt_est_rd ;
		rename sen_post se_rd ;
		gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
		gen sta_sign_rd = "" ;
		replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
		replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
		replace sta_sign_rd = "***" if star_rd <= 0.01 ;
		drop star_rd ;
	};
			
	else {;
				
		rename n_age18unfav pt_est_iv ;
		rename sen_age18unfav se_iv ;
		gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
		gen sta_sign_iv = "" ;
		replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
		replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
		replace sta_sign_iv = "***" if star_iv <= 0.01 ;
		drop star_iv ;
	};
			
			
	order variables pt_est* se* sta_sign* mean N flag_int;
			
	tostring flag_int, replace ;
			
	replace flag_int = "_in" if flag_int == "1" ;
	replace flag_int = "_ex" if flag_int == "0" ;
			
	drop length ;
			
	reshape wide pt_est* se* sta_sign* mean N, i(variables) j(flag_int) string ;

	tostring se*, replace ;
	replace se_`method'_ex = "(" + se_`method'_ex + ")" ;
	replace se_`method'_in = "(" + se_`method'_in + ")" ;
			
	tempfile all_`method'_all ;
	save `all_`method'_all' ;		

}; 
		
use `all_rd_all', clear ;
		
merge 1:1 variables using `all_iv_all' ;
drop _merge ;
		
		
order variables pt_est_rd_ex sta_sign_rd_ex se_rd_ex pt_est_iv_ex sta_sign_iv_ex se_iv_ex mean_ex N_ex
	  pt_est_rd_in sta_sign_rd_in se_rd_in pt_est_iv_in sta_sign_iv_in se_iv_in mean_in N_in ;
		
		
gen order = 1 if variables == "Any charge" ;
replace order = 2 if variables == "Income-generating charge" ;
replace order = 3 if variables == "Non-income-generating" ;
replace order = 4 if variables == "Incarcerated (annual likelihood/days)" ;

			
sort order ;
	
drop order ;
		
tempfile compiled_all_combsib ;
save `compiled_all_combsib' ;		


/* ********************** */
/* **** CREATE TABLE **** */
/* ********************** */
   
use `compiled_all_sib', clear ;
append using `compiled_all_combsib' ;
append using `compiled_all_par' ;

insobs 1, before(1) ;
replace variables = "Sibling sample" in 1 ;
insobs 1, before(6) ;
replace variables = "18-year-old and sibling sample" in 6 ;
insobs 1, before(11) ;
replace variables = "Parent sample" in 11 ;

export excel using "`output'/tabB16.xls", firstrow(variables) ;
